package java.hilyb.dbtest;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import java.hilyb.dbtest.domain.Category;
import java.hilyb.dbtest.domain.Record;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * User: wlai
 * Date: 12/4/2014
 * Time: 11:39 AM
 */
public class DBHelper extends SQLiteOpenHelper {
    public static final String DB_NAME = "dbtest.db";
    public static final int DB_VERSION = 5;

    public DBHelper(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.w("DB", "Creating database.");
        db.execSQL(Category.CREATE_TABLE_SQL);
        db.execSQL(Record.CREATE_TABLE_SQL);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i2) {
        Log.w("DB", "Upgrading database.");
        db.execSQL("DROP TABLE IF EXISTS " + Category.TABLE_NAME);
        db.execSQL("DROP TABLE IF EXISTS " + Record.TABLE_NAME);
        onCreate(db);
    }

    public void addRecord(Record record) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(Record.C_CONSUMED, record.getConsumed());
        cv.put(Record.C_TIME, record.getRecordTime().getTime());
        cv.put(Record.C_MEMO, record.getMemo());
        cv.put(Record.C_CATEGORY_ID, record.getCategoryId());

        db.insert(Record.TABLE_NAME, Record.C_ID, cv);
        db.close();
    }

    public List<Record> findRecordByCategory(Integer categoryId) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from " + Record.TABLE_NAME + " where category_id=?", new String[]{categoryId.toString()});
        List<Record> list = new ArrayList<Record>();
        while (cursor.moveToNext()) {
            int c1 = cursor.getColumnIndex(Record.C_ID);
            int c2 = cursor.getColumnIndex(Record.C_CONSUMED);
            int c3 = cursor.getColumnIndex(Record.C_CATEGORY_ID);
            int c4 = cursor.getColumnIndex(Record.C_TIME);
            int c5 = cursor.getColumnIndex(Record.C_MEMO);
            Record record = new Record();
            record.setId(cursor.getInt(c1));
            record.setConsumed(cursor.getFloat(c2));
            record.setCategoryId(cursor.getInt(c3));
            record.setRecordTime(new Date(cursor.getLong(c4)));
            record.setMemo(cursor.getString(c5));
            list.add(record);
        }
        cursor.close();
        db.close();
        return list;
    }

    public List<Record> findRecordByDay(Date start, Date end) {
        SQLiteDatabase db = this.getReadableDatabase();
        String s1 = new Long(start.getTime()).toString();
        String s2 = new Long(end.getTime()).toString();
        Log.w("DB", s1 + "------" + s2);
        Cursor cursor = db.rawQuery("select * from " + Record.TABLE_NAME + " where record_time>=? and record_time<=?", new String[]{s1, s2});
        List<Record> list = new ArrayList<Record>();
        while (cursor.moveToNext()) {
            int c1 = cursor.getColumnIndex(Record.C_ID);
            int c2 = cursor.getColumnIndex(Record.C_CONSUMED);
            int c3 = cursor.getColumnIndex(Record.C_CATEGORY_ID);
            int c4 = cursor.getColumnIndex(Record.C_TIME);
            int c5 = cursor.getColumnIndex(Record.C_MEMO);
            Record record = new Record();
            record.setId(cursor.getInt(c1));
            record.setConsumed(cursor.getFloat(c2));
            record.setCategoryId(cursor.getInt(c3));
            record.setRecordTime(new Date(cursor.getLong(c4)));
            record.setMemo(cursor.getString(c5));
            list.add(record);
        }
        cursor.close();
        db.close();
        Log.w("DB", list.size() + "------");
        return list;
    }

    public void addCategory(String name, Integer parentId) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(Category.C_NAME, name);
        if (parentId != null) cv.put(Category.C_PARENT, parentId);
        db.insert(Category.TABLE_NAME, Category.C_ID, cv);
        db.close();
    }

    public Category getCategoriesById(int categoryId) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(Category.TABLE_NAME, new String[]{Category.C_ID, Category.C_NAME}, "id=?", new String[]{categoryId + ""}, null, null, Category.C_ID);
        Category c = null;
        while (cursor.moveToNext()) {
            c = cursorToCategory(cursor);
        }
        cursor.close();
        return c;
    }

    private Category cursorToCategory(Cursor cursor) {
        Category c;
        int idxId = cursor.getColumnIndex(Category.C_ID);
        int idxName = cursor.getColumnIndex(Category.C_NAME);
        int idxParent=cursor.getColumnIndex(Category.C_PARENT);

        String name = cursor.getString(idxName);
        Integer id = cursor.getInt(idxId);
        c = new Category(id, name);
        c.setParentId(cursor.getInt(idxParent));
        return c;
    }

    public List<Category> getCategories() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(Category.TABLE_NAME, new String[]{Category.C_ID, Category.C_NAME}, null, null, null, null, Category.C_ID);
        List<Category> categories = new ArrayList<Category>();
        while (cursor.moveToNext()) {
            Category c = cursorToCategory(cursor);
            categories.add(c);
        }
        cursor.close();
        return categories;
    }
    public List<Category> getTopCategories() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(Category.TABLE_NAME, new String[]{Category.C_ID, Category.C_NAME}, Category.C_PARENT+" is null", null, null, null, Category.C_ID);
        List<Category> categories = new ArrayList<Category>();
        while (cursor.moveToNext()) {
            Category c = cursorToCategory(cursor);
            categories.add(c);
        }
        cursor.close();

        return categories;
    }
    public Cursor getCategories2() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(Category.TABLE_NAME, new String[]{Category.C_ID, Category.C_NAME}, null, null, null, null, Category.C_ID);
        return cursor;
    }



    public boolean removeCategory(Category c) {
        SQLiteDatabase db = this.getWritableDatabase();
        int res = db.delete(Category.TABLE_NAME, Category.C_ID + "=? ", new String[]{c.getId().toString()});
        db.close();
        return res > 0;
    }

    public boolean updateCategory(Category c) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(Category.C_ID, c.getId());
        cv.put(Category.C_NAME, c.getName());
        int res = db.update(Category.TABLE_NAME, cv, Category.C_ID + "=? ", new String[]{c.getId().toString()});
        db.close();
        return res > 0;

    }


    public void addCategory(Category category) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put(Category.C_PICID,category.getPicId());
        cv.put(Category.C_NAME, category.getName());
        if (category.getParentId() != null) cv.put(Category.C_PARENT, category.getParentId());
        db.insert(Category.TABLE_NAME, Category.C_ID, cv);
        db.close();
    }
}
